﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;

namespace CCPSIP
{
    public class OracleKitMsg
    {
        private const string kOracleServer = "ISENDER.CCP.COM.TW";
        private const string kOracleUserId = "ISENDER";
        private static byte[] kOracleKey = { 0x61, 0x4B, 0x68, 0x4A, 0x4A, 0x46, 0x41, 0x32, 0x73, 0x57, 0x66, 0x45, 0x73, 0x51, 0x7A, 0x30, 0x5A, 0x6D, 0x35, 0x69, 0x77, 0x51, 0x69, 0x39, 0x67, 0x6A, 0x69, 0x56, 0x4E, 0x44 };

        private static string GetConnStrMIS()
        {
            return String.Format(
              "Data Source={0};Persist Security Info=True;User ID={1};Password={2}",
              kOracleServer, kOracleUserId, System.Text.Encoding.UTF8.GetString(kOracleKey));
        }

        /// <summary>
        /// Insert訊息到 msg_one Table，回傳Server端的訊息ID
        /// </summary>
        /// <param name="sender">發訊方SIP帳號</param>
        /// <param name="receiver">收訊方SIP帳號</param>
        /// <param name="msgType">訊息種類代號</param>
        /// <param name="msgPreview">訊息摘要</param>
        /// <param name="message">訊息全文</param>
        /// <param name="localPID">發訊方的本機訊息ID</param>
        /// <param name="msgTime">儲存訊息時間</param>
        /// <returns>Server端的訊息ID</returns>
        public static long InsertMsgOne(string sender, string receiver, string msgType, string msgPreview, string message, int localPID, DateTime msgTime)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string insStr = "INSERT INTO msg_one"
                  + " (CreateTime, Status, Sender, Receiver, MsgType, MsgPreview, MsgContent, LocalPID) VALUES"
                  + " (:CreateTime, '0', :Sender, :Receiver, :MsgType, :MsgPreview, :MsgContent, :LocalPID)"
                  + " RETURNING pid INTO :pid";
                OracleCommand insCmd = new OracleCommand(insStr, connOracle);
                insCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = msgTime;
                insCmd.Parameters.Add("Sender", OracleType.VarChar).Value = sender;
                insCmd.Parameters.Add("Receiver", OracleType.VarChar).Value = receiver;
                insCmd.Parameters.Add("MsgType", OracleType.VarChar).Value = msgType;
                insCmd.Parameters.Add("MsgPreview", OracleType.VarChar).Value = msgPreview;
                insCmd.Parameters.Add("MsgContent", OracleType.Clob).Value = message;
                insCmd.Parameters.Add("LocalPID", OracleType.Int32).Value = localPID;

                insCmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = ":pid",
                    OracleType = OracleType.Number,
                    Direction = ParameterDirection.Output
                });

                insCmd.ExecuteNonQuery();

                return (long)Convert.ToInt32(insCmd.Parameters[":pid"].Value);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 寫入訊息到 msg_one Table (Waiting)，回傳Server端的訊息ID
        /// </summary>
        /// <param name="sender">發訊方SIP帳號</param>
        /// <param name="receiver">收訊方SIP帳號</param>
        /// <param name="msgType">訊息種類代號</param>
        /// <param name="msgPreview">訊息摘要</param>
        /// <param name="localPID">發訊方的本機訊息ID</param>
        /// <param name="msgTime">儲存訊息時間</param>
        /// <returns>Server端的訊息ID</returns>
        public static long InsertMsgOneWait(string sender, string receiver, string msgType, string msgPreview, int localPID, DateTime msgTime)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string insStr = "INSERT INTO msg_one"
                  + " (CreateTime, Status, Sender, Receiver, MsgType, MsgPreview, MsgContent, LocalPID) VALUES"
                  + " (:CreateTime, 'W', :Sender, :Receiver, :MsgType, :MsgPreview, :MsgContent, :LocalPID)"
                  + " RETURNING pid INTO :pid";
                OracleCommand insCmd = new OracleCommand(insStr, connOracle);
                insCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = msgTime;
                insCmd.Parameters.Add("Sender", OracleType.VarChar).Value = sender;
                insCmd.Parameters.Add("Receiver", OracleType.VarChar).Value = receiver;
                insCmd.Parameters.Add("MsgType", OracleType.VarChar).Value = msgType;
                insCmd.Parameters.Add("MsgPreview", OracleType.VarChar).Value = msgPreview;
                insCmd.Parameters.Add("MsgContent", OracleType.Clob).Value = "TO_UPDATE";
                insCmd.Parameters.Add("LocalPID", OracleType.Int32).Value = localPID;
                insCmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = ":pid",
                    OracleType = OracleType.Number,
                    Direction = ParameterDirection.Output
                });
                insCmd.ExecuteNonQuery();
                return (long)Convert.ToInt32(insCmd.Parameters[":pid"].Value);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得未讀訊息列表(單人+群組聊天)
        /// </summary>
        /// <param name="userInfo">收訊方SipUserInfo物件</param>
        /// <returns>未讀訊息 Dictionary Array</returns>
        public static Dictionary<string, string>[] getUnreadList(SipUserInfo userInfo)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());

            try
            {
                connOracle.Open();
                List<UnreadMsgPreview> previewList = new List<UnreadMsgPreview>();

                //【01】抓取單人聊天訊息
                //
                string selStr1 = "SELECT Sender AS ChatID, COUNT(*) AS UnreadCount,"
                  + " MAX(CreateTime) AS LastTime, MAX(pid) AS LastMsgID,"
                  + " MIN(CreateTime) AS FirstTime"
                  + " FROM msg_one"
                  + " WHERE Status IN ('0','2') AND ReadTime IS NULL AND Receiver=:Receiver"
                  + " GROUP BY Sender";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                selCmd1.Parameters.Add("Receiver", OracleType.VarChar).Value = userInfo.SIP_Num;
                OracleDataAdapter adapter1 = new OracleDataAdapter(selCmd1);
                DataSet dataSet1 = new DataSet();
                adapter1.Fill(dataSet1);

                foreach (DataRow row in dataSet1.Tables[0].Rows)
                {
                    string selStr2 = "SELECT MsgPreview FROM msg_one WHERE pid=:MsgID";
                    OracleCommand selCmd2 = new OracleCommand(selStr2, connOracle);
                    selCmd2.Parameters.Add("MsgID", OracleType.Int32).Value = row["LastMsgID"];
                    string lastPreview = Convert.ToString(selCmd2.ExecuteScalar());

                    UnreadMsgPreview previewObj = new UnreadMsgPreview(row, lastPreview);
                    previewList.Add(previewObj);
                }


                //【02】抓取群組聊天訊息
                //
                string selStr3 = "SELECT c.ClientGID AS ChatID, COUNT(*) AS UnreadCount,"
                  + " MAX(m.CreateTime) AS LastTime, MAX(r.GMsgID) AS LastMsgID,"
                  + " MIN(m.CreateTime) AS FirstTime"
                  + " FROM group_msg_read r, group_msg m, group_chat c"
                  + " WHERE r.GMsgID=m.pid AND m.GroupID=c.pid"
                  + "   AND r.Receiver=:Receiver AND r.ReadTime IS NULL AND m.Status NOT IN ('W','F','L')"
                  + " GROUP BY c.ClientGID";
                OracleCommand selCmd3 = new OracleCommand(selStr3, connOracle);
                selCmd3.Parameters.Add("Receiver", OracleType.VarChar).Value = userInfo.SIP_Num;
                OracleDataAdapter adapter3 = new OracleDataAdapter(selCmd3);
                DataSet dataSet3 = new DataSet();
                adapter3.Fill(dataSet3);

                foreach (DataRow row in dataSet3.Tables[0].Rows)
                {
                    string selStr4 = "SELECT MsgPreview FROM group_msg WHERE pid=:MsgID";
                    OracleCommand selCmd4 = new OracleCommand(selStr4, connOracle);
                    selCmd4.Parameters.Add("MsgID", OracleType.Int32).Value = row["LastMsgID"];
                    string lastPreview = Convert.ToString(selCmd4.ExecuteScalar());

                    UnreadMsgPreview previewObj = new UnreadMsgPreview(row, lastPreview);
                    previewList.Add(previewObj);
                }


                //【98】依訊息時間反排(新的在最上面)
                //
                previewList.Sort();
                previewList.Reverse();


                //【99】轉換為Dictionary回傳
                //
                Dictionary<string, string>[] resultArray = new Dictionary<string, string>[previewList.Count];
                for (int i = 0; i < resultArray.Length; i++)
                {
                    resultArray[i] = new Dictionary<string, string>();
                    string ChatID = previewList[i].ChatID;
                    resultArray[i]["ChatID"] = ChatID;

                    if (SipKit.isGroupID(ChatID))
                        resultArray[i]["ChatName"] = OracleKitGroup.getGroupName(ChatID);
                    else if (RobotKit.isRobot(ChatID))
                        resultArray[i]["ChatName"] = RobotKit.getRobotCName(ChatID);
                    else
                        resultArray[i]["ChatName"] = SipKit.getCustomName(ChatID);

                    resultArray[i]["UnreadCount"] = Convert.ToString(previewList[i].UnreadCount);
                    resultArray[i]["LastTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", previewList[i].LastTime);
                    resultArray[i]["LastPreview"] = previewList[i].LastPreview;
                    resultArray[i]["FirstTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", previewList[i].FirstTime);
                }
                return resultArray;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 讀取未讀訊息(單人聊天)
        /// </summary>
        /// <param name="userInfo">收訊方SipUserInfo物件</param>
        /// <param name="sender">發訊方帳號</param>
        /// <returns>未讀訊息 Dictionary</returns>
        public static Dictionary<string, object> ReadOneOneMsgs(SipUserInfo userInfo, string sender)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());

            try
            {
                connOracle.Open();

                //【01】取得未讀訊息
                //
                string selStr = "SELECT pid, CreateTime, MsgType, MsgContent"
                  + " FROM msg_one"
                  + " WHERE Status IN ('0','2') AND ReadTime IS NULL"
                  + "   AND Sender=:Sender AND Receiver=:Receiver"
                  + " ORDER BY CreateTime, pid";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("Sender", OracleType.VarChar).Value = sender;
                selCmd.Parameters.Add("Receiver", OracleType.VarChar).Value = userInfo.SIP_Num;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<Dictionary<string, string>> MessageList = new List<Dictionary<string, string>>();
                List<string> MsgIdList = new List<string>();

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    Dictionary<string, string> message = new Dictionary<string, string>();
                    message["MsgID"] = Convert.ToString(row["pid"]);
                    message["MsgTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", row["CreateTime"]);
                    message["MsgType"] = Convert.ToString(row["MsgType"]);
                    message["MsgSender"] = sender;
                    message["Content"] = Convert.ToString(row["MsgContent"]);
                    MessageList.Add(message);

                    MsgIdList.Add(message["MsgID"]);
                }

                if (MsgIdList.Count > 0)
                {
                    //【02-1】更新訊息狀態,讀取時間(正常)
                    //
                    string updStr1 = "UPDATE msg_one SET"
                      + " ReadTime=:DateTime, Status='4'"
                      + " WHERE Status IN ('0','2') AND ReadTime IS NULL"
                      + "   AND ReadTagTime IS NULL AND pid IN "
                      + string.Format("({0})", string.Join(",", MsgIdList.ToArray()));
                    OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle);
                    updCmd1.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    updCmd1.ExecuteNonQuery();


                    //【02-2】更新訊息狀態,讀取時間(發訊人已離開)
                    //
                    string updStr2 = "UPDATE msg_one SET"
                      + " ReadTime=:DateTime, Status='L', ReadTagTime=NULL"
                      + " WHERE Status IN ('0','2') AND ReadTime IS NULL"
                      + "   AND ReadTagTime IS NOT NULL AND pid IN "
                      + string.Format("({0})", string.Join(",", MsgIdList.ToArray()));
                    OracleCommand updCmd2 = new OracleCommand(updStr2, connOracle);
                    updCmd2.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    updCmd2.ExecuteNonQuery();
                }


                //【99】回傳結果
                //
                Dictionary<string, object> resultDict = new Dictionary<string, object>();
                resultDict["ChatID"] = sender;
                resultDict["Receiver"] = userInfo.SIP_Num;
                resultDict["Messages"] = MessageList.ToArray();
                return resultDict;
            }
            finally
            {
                connOracle.Close();
            }
        }

        /// <summary>
        /// 讀取未讀訊息(單人聊天) Test 
        /// </summary>
        /// <param name="userInfo">收訊方SipUserInfo物件</param>
        /// <param name="sender">發訊方帳號</param>
        /// <returns>未讀訊息 Dictionary</returns>
        public static Dictionary<string, object> ReadOneOneMsgs_ByID(SipUserInfo userInfo, string sender, string receiveID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());

            try
            {
                connOracle.Open();

                //【01】取得未讀訊息
                //
                string selStr = "SELECT pid, CreateTime, MsgType, MsgContent, MsgPreview"
                  + " FROM msg_one"
                  + " WHERE CreateTime >:ReceiveID AND Status NOT IN ('F','L')"
                  + "   AND Sender=:Sender AND Receiver=:Receiver"
                  + " ORDER BY CreateTime, pid";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("ReceiveID", OracleType.DateTime).Value = receiveID;
                selCmd.Parameters.Add("Sender", OracleType.VarChar).Value = sender;
                selCmd.Parameters.Add("Receiver", OracleType.VarChar).Value = userInfo.SIP_Num;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<Dictionary<string, string>> MessageList = new List<Dictionary<string, string>>();
                List<string> MsgIdList = new List<string>();

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    Dictionary<string, string> message = new Dictionary<string, string>();
                    message["MsgID"] = Convert.ToString(row["pid"]);
                    message["MsgTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", row["CreateTime"]);
                    message["MsgType"] = Convert.ToString(row["MsgType"]);
                    message["MsgSender"] = sender;
                    message["Content"] = Convert.ToString(row["MsgContent"]);
                    message["MsgPreview"] = Convert.ToString(row["MsgPreview"]);
                    MessageList.Add(message);

                    MsgIdList.Add(message["MsgID"]);
                }

                if (MsgIdList.Count > 0)
                {
                    //【02-1】更新訊息狀態,讀取時間(正常)
                    //
                    string updStr1 = "UPDATE msg_one SET"
                      + " ReadTime=:DateTime, Status='4'"
                      + " WHERE Status IN ('0','2') AND ReadTime IS NULL"
                      + "   AND ReadTagTime IS NULL AND pid IN "
                      + string.Format("({0})", string.Join(",", MsgIdList.ToArray()));
                    OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle);
                    updCmd1.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    updCmd1.ExecuteNonQuery();


                    //【02-2】更新訊息狀態,讀取時間(發訊人已離開)
                    //
                    string updStr2 = "UPDATE msg_one SET"
                      + " ReadTime=:DateTime, Status='L', ReadTagTime=NULL"
                      + " WHERE Status IN ('0','2') AND ReadTime IS NULL"
                      + "   AND ReadTagTime IS NOT NULL AND pid IN "
                      + string.Format("({0})", string.Join(",", MsgIdList.ToArray()));
                    OracleCommand updCmd2 = new OracleCommand(updStr2, connOracle);
                    updCmd2.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    updCmd2.ExecuteNonQuery();
                }


                //【99】回傳結果
                //
                Dictionary<string, object> resultDict = new Dictionary<string, object>();
                resultDict["ChatID"] = sender;
                resultDict["Receiver"] = userInfo.SIP_Num;
                resultDict["Messages"] = MessageList.ToArray();
                return resultDict;
            }
            finally
            {
                connOracle.Close();
            }
        }

        /// <summary>
        /// 讀取 Read Tag(單人訊息)
        /// </summary>
        /// <param name="userInfo">發訊方SipUserInfo物件</param>
        /// <param name="Receiver">收訊方帳號</param>
        /// <returns>Read Tag Dictionary</returns>
        public static Dictionary<string, object> TagOneOneMsgRead(SipUserInfo userInfo, string Receiver)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                //【01】取得 Read Tag
                //
                string selStr = "SELECT pid, LocalPID, CreateTime, ReadTime FROM msg_one"
                  + " WHERE Status NOT IN ('F','L')"
                  + " AND ReadTime IS NOT NULL AND ReadTagTime IS NULL"
                  + " AND Sender=:Sender AND Receiver=:Receiver"
                  + " ORDER BY CreateTime, pid";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("Sender", OracleType.VarChar).Value = userInfo.SIP_Num;
                selCmd.Parameters.Add("Receiver", OracleType.VarChar).Value = Receiver;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<Dictionary<string, string>> TagList = new List<Dictionary<string, string>>();
                List<string> MsgIdList = new List<string>();

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    Dictionary<string, string> readTag = new Dictionary<string, string>();
                    readTag["MsgID"] = Convert.ToString(row["pid"]);
                    readTag["LocalPID"] = Convert.ToString(row["LocalPID"]);
                    //readTag["MsgTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", row["CreateTime"]);
                    readTag["ReadTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", row["ReadTime"]);
                    TagList.Add(readTag);

                    MsgIdList.Add(readTag["MsgID"]);
                }

                if (MsgIdList.Count > 0)
                {
                    //【02】更新訊息狀態, Read Tag時間
                    //
                    string updStr = "UPDATE msg_one SET ReadTagTime=:DateTime, Status='F'"
                      + " WHERE ReadTagTime IS NULL AND pid IN "
                      + string.Format("({0})", string.Join(",", MsgIdList.ToArray()));
                    OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                    updCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    updCmd.ExecuteNonQuery();
                }

                //【99】回傳結果
                //
                Dictionary<string, object> resultDict = new Dictionary<string, object>();
                resultDict["ChatID"] = Receiver;
                resultDict["Sender"] = userInfo.SIP_Num;
                resultDict["ReadTags"] = TagList.ToArray();
                return resultDict;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 儲存 User 上傳的檔名
        /// </summary>
        /// <param name="sender">發訊方SIP帳號</param>
        /// <param name="MsgID">Server端的訊息ID</param>
        /// <param name="FileNameMsg">上傳的檔名(可能多個)</param>
        public static void UpdateMsgOneFilename(string sender, long MsgID, string FileNameMsg)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string updStr = "UPDATE msg_one"
                  + " SET Status='0', MsgContent=:MsgContent"
                  + " WHERE pid=:MsgID AND Status='W' AND Sender=:Sender";
                OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                updCmd.Parameters.Add("MsgContent", OracleType.Clob).Value = FileNameMsg;
                updCmd.Parameters.Add("MsgID", OracleType.Int32).Value = MsgID;
                updCmd.Parameters.Add("Sender", OracleType.VarChar).Value = sender;
                updCmd.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 檢查訊息檔案存取權限(單人訊息)
        /// </summary>
        /// <param name="userInfo">SipUserInfo物件</param>
        /// <param name="MsgID">Server端的訊息ID</param>
        /// <param name="filename">要存取的檔案名稱</param>
        /// <returns>可以存取true，否則false</returns>
        public static bool canAccessFile(SipUserInfo userInfo, long MsgID, string filename)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string selStr = "SELECT MsgContent FROM msg_one"
                  + " WHERE pid=:MsgID AND :SIP_Num IN (Sender, Receiver)";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("MsgID", OracleType.Int32).Value = MsgID;
                selCmd.Parameters.Add("SIP_Num", OracleType.VarChar).Value = userInfo.SIP_Num;
                string MsgContent = Convert.ToString(selCmd.ExecuteScalar());

                if (string.IsNullOrEmpty(MsgContent))
                    return false;
                else if (MsgContent.Contains(filename))
                    return true;
                else
                    return false;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// User離開單人聊天(刪除對話記錄)相關動作
        /// </summary>
        /// <param name="userInfo">SipUserInfo物件</param>
        /// <param name="ChatID">對方帳號</param>
        public static void LeaveChat(SipUserInfo userInfo, string ChatID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                //【01】放棄取得已讀標記(收訊人已讀)
                //
                string updStr1 = "UPDATE msg_one SET Status='L'"
                  + " WHERE Receiver=:Receiver AND Sender=:Sender AND Status='4'";
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle);
                updCmd1.Parameters.Add("Receiver", OracleType.VarChar).Value = ChatID;
                updCmd1.Parameters.Add("Sender", OracleType.VarChar).Value = userInfo.SIP_Num;
                updCmd1.ExecuteNonQuery();

                //【02】放棄取得已讀標記(收訊人未讀)
                //
                string updStr2 = "UPDATE msg_one SET ReadTagTime=:DateTime"
                  + " WHERE Receiver=:Receiver AND Sender=:Sender AND Status IN ('0','2')";
                OracleCommand updCmd2 = new OracleCommand(updStr2, connOracle);
                updCmd2.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                updCmd2.Parameters.Add("Receiver", OracleType.VarChar).Value = ChatID;
                updCmd2.Parameters.Add("Sender", OracleType.VarChar).Value = userInfo.SIP_Num;
                updCmd2.ExecuteNonQuery();


                //【03】Reset訊息提醒狀態
                //
                SetMsgNotify(userInfo.SIP_Num, ChatID, true);
            }
            finally
            {
                connOracle.Close();
            }
        }



        #region Group Chat Message

        /// <summary>
        /// 寫入訊息到群組聊天，回傳Server端的訊息ID
        /// </summary>
        /// <param name="userInfo">發訊方SipUserInfo物件</param>
        /// <param name="ClientGID">Client端的群組ID</param>
        /// <param name="msgType">訊息種類代號</param>
        /// <param name="msgPreview">訊息摘要</param>
        /// <param name="message">訊息全文</param>
        /// <param name="localPID">發訊方的本機訊息ID</param>
        /// <param name="msgTime">儲存訊息時間</param>
        /// <returns>Server端的訊息ID</returns>
        public static long InsertGroupMsg(SipUserInfo userInfo, string ClientGID, string msgType, string msgPreview, string message, int localPID, DateTime msgTime)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();

                int GroupID = OracleKitGroup.getGroupID(ClientGID, connOracle);
                //【02】儲存群組訊息待讀取Table
                //
                ChatGroupMember[] Members = OracleKitGroup.getMembers(ClientGID, connOracle);

                myTrans = connOracle.BeginTransaction();

                //【01】寫入group_msg
                //
                string insStr = "INSERT INTO group_msg"
                  + " (CreateTime, Status, Sender, GroupID, MsgType, MsgPreview, MsgContent, LocalPID) VALUES"
                  + " (:CreateTime, '0', :Sender, :GroupID, :MsgType, :MsgPreview, :MsgContent, :LocalPID)"
                  + " RETURNING pid INTO :pid";
                OracleCommand insCmd = new OracleCommand(insStr, connOracle, myTrans);
                insCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = msgTime;
                insCmd.Parameters.Add("Sender", OracleType.VarChar).Value = userInfo.SIP_Num;
                insCmd.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                insCmd.Parameters.Add("MsgType", OracleType.VarChar).Value = msgType;
                insCmd.Parameters.Add("MsgPreview", OracleType.VarChar).Value = msgPreview;
                insCmd.Parameters.Add("MsgContent", OracleType.Clob).Value = message;
                insCmd.Parameters.Add("LocalPID", OracleType.Int32).Value = localPID;

                insCmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = ":pid",
                    OracleType = OracleType.Number,
                    Direction = ParameterDirection.Output
                });

                insCmd.ExecuteNonQuery();

                long GMsgID = (long)Convert.ToInt32(insCmd.Parameters[":pid"].Value);



                foreach (ChatGroupMember member in Members)
                {
                    if (member.MemberID == userInfo.SIP_Num)
                    {
                        continue;//略過發訊人
                    }
                    string insStrRead = "INSERT INTO group_msg_read"
                      + " (GMsgID, Receiver, GroupID) VALUES (:GMsgID, :Receiver, :GroupID)";
                    OracleCommand insCmdRead = new OracleCommand(insStrRead, connOracle, myTrans);
                    insCmdRead.Parameters.Add("GMsgID", OracleType.Int32).Value = GMsgID;
                    insCmdRead.Parameters.Add("Receiver", OracleType.VarChar).Value = member.MemberID;
                    insCmdRead.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                    insCmdRead.ExecuteNonQuery();
                }

                //【99】Commit Transaction
                //
                myTrans.Commit();

                return GMsgID;
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 寫入訊息到群組聊天Table (Waiting)，回傳Server端的訊息ID
        /// </summary>
        /// <param name="userInfo">發訊方SipUserInfo物件</param>
        /// <param name="ClientGID">Client端的群組ID</param>
        /// <param name="msgType">訊息種類代號</param>
        /// <param name="msgPreview">訊息摘要</param>
        /// <param name="localPID">發訊方的本機訊息ID</param>
        /// <param name="msgTime">儲存訊息時間</param>
        /// <returns>Server端的訊息ID</returns>
        public static long InsertGroupMsgWait(SipUserInfo userInfo, string ClientGID, string msgType, string msgPreview, int localPID, DateTime msgTime)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();

                int GroupID = OracleKitGroup.getGroupID(ClientGID, connOracle);

                //【02】儲存群組訊息待讀取Table
                //
                ChatGroupMember[] Members = OracleKitGroup.getMembers(ClientGID, connOracle);

                myTrans = connOracle.BeginTransaction();

                //【01】寫入group_msg
                //
                string insStr = "INSERT INTO group_msg"
                  + " (CreateTime, Status, Sender, GroupID, MsgType, MsgPreview, MsgContent, LocalPID) VALUES"
                  + " (:CreateTime, 'W', :Sender, :GroupID, :MsgType, :MsgPreview, :MsgContent, :LocalPID)"
                  + " RETURNING pid INTO :pid";
                OracleCommand insCmd = new OracleCommand(insStr, connOracle, myTrans);
                insCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = msgTime;
                insCmd.Parameters.Add("Sender", OracleType.VarChar).Value = userInfo.SIP_Num;
                insCmd.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                insCmd.Parameters.Add("MsgType", OracleType.VarChar).Value = msgType;
                insCmd.Parameters.Add("MsgPreview", OracleType.VarChar).Value = msgPreview;
                insCmd.Parameters.Add("MsgContent", OracleType.Clob).Value = "TO_UPDATE";
                insCmd.Parameters.Add("LocalPID", OracleType.Int32).Value = localPID;
                insCmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = ":pid",
                    OracleType = OracleType.Number,
                    Direction = ParameterDirection.Output
                });
                insCmd.ExecuteNonQuery();

                long GMsgID = (long)Convert.ToInt32(insCmd.Parameters[":pid"].Value);

                foreach (ChatGroupMember member in Members)
                {
                    if (member.MemberID == userInfo.SIP_Num)
                    {
                        continue;//略過發訊人
                    }
                    string insStrRead = "INSERT INTO group_msg_read"
                      + " (GMsgID, Receiver, GroupID) VALUES (:GMsgID, :Receiver, :GroupID)";
                    OracleCommand insCmdRead = new OracleCommand(insStrRead, connOracle, myTrans);
                    insCmdRead.Parameters.Add("GMsgID", OracleType.Int32).Value = GMsgID;
                    insCmdRead.Parameters.Add("Receiver", OracleType.VarChar).Value = member.MemberID;
                    insCmdRead.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                    insCmdRead.ExecuteNonQuery();
                }

                //【99】Commit Transaction
                //
                myTrans.Commit();

                return GMsgID;
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 儲存 User 上傳的檔名
        /// </summary>
        /// <param name="userInfo">發訊方SipUserInfo物件</param>
        /// <param name="GMsgID">Server端的群組訊息ID</param>
        /// <param name="FileNameMsg">上傳的檔名(可能多個)</param>
        public static void UpdateGroupMsgFilename(SipUserInfo userInfo, long GMsgID, string FileNameMsg)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string updStr = "UPDATE group_msg"
                  + " SET Status='0', MsgContent=:MsgContent"
                  + " WHERE pid=:GMsgID AND Status='W' AND Sender=:Sender";
                OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                updCmd.Parameters.Add("MsgContent", OracleType.Clob).Value = FileNameMsg;
                updCmd.Parameters.Add("GMsgID", OracleType.Int32).Value = GMsgID;
                updCmd.Parameters.Add("Sender", OracleType.VarChar).Value = userInfo.SIP_Num;
                updCmd.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 讀取未讀訊息(群組聊天)
        /// </summary>
        /// <param name="userInfo">收訊方SipUserInfo物件</param>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <returns>未讀訊息 Dictionary</returns>
        public static Dictionary<string, object> ReadGroupMsgs(SipUserInfo userInfo, string clientGID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                int GroupID = OracleKitGroup.getGroupID(clientGID, connOracle);

                //【01】取得未讀訊息
                //
                string selStr = "SELECT r.GMsgID, m.CreateTime, m.MsgType,"
                  + " m.Sender, m.MsgContent, m.UpdateObj"
                  + " FROM group_msg_read r, group_msg m"
                  + " WHERE r.GMsgID=m.pid AND r.Receiver=:Receiver AND m.GroupID=:GroupID"
                  + "   AND r.ReadTime IS NULL AND m.Status<>'W'"
                  + " ORDER BY m.CreateTime, r.GMsgID";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("Receiver", OracleType.VarChar).Value = userInfo.SIP_Num;
                selCmd.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<Dictionary<string, string>> MessageList = new List<Dictionary<string, string>>();
                List<string> MsgIdList = new List<string>();

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    Dictionary<string, string> message = new Dictionary<string, string>();
                    string MsgType = Convert.ToString(row["MsgType"]);
                    message["MsgID"] = Convert.ToString(row["GMsgID"]);
                    message["MsgTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", row["CreateTime"]);
                    message["MsgType"] = MsgType;
                    message["MsgSender"] = Convert.ToString(row["Sender"]);
                    message["Content"] = Convert.ToString(row["MsgContent"]);
                    if (MsgType == "GJ" || MsgType == "GL")
                    {
                        message["UpdateObj"] = Convert.ToString(row["UpdateObj"]);
                    }
                    MessageList.Add(message);

                    MsgIdList.Add(message["MsgID"]);
                }


                if (MsgIdList.Count > 0)
                {
                    //【02】寫入讀取時間
                    //
                    string updStr = "UPDATE group_msg_read SET ReadTime=:DateTime"
                      + " WHERE ReadTime IS NULL AND Receiver=:Receiver AND GMsgID IN "
                      + string.Format("({0})", string.Join(",", MsgIdList.ToArray()));
                    OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                    updCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    updCmd.Parameters.Add("Receiver", OracleType.VarChar).Value = userInfo.SIP_Num;
                    updCmd.ExecuteNonQuery();
                }


                //【99】回傳結果
                //
                Dictionary<string, object> resultDict = new Dictionary<string, object>();
                resultDict["ChatID"] = clientGID;
                resultDict["Receiver"] = userInfo.SIP_Num;
                resultDict["Messages"] = MessageList.ToArray();
                return resultDict;
            }
            finally
            {
                connOracle.Close();
            }
        }

        /// <summary>
        /// 讀取未讀訊息(群組聊天) Test 
        /// </summary>
        /// <param name="userInfo">收訊方SipUserInfo物件</param>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <returns>未讀訊息 Dictionary</returns>
        public static Dictionary<string, object> ReadGroupMsgs_ByID(SipUserInfo userInfo, string clientGID, string receiveID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                int GroupID = OracleKitGroup.getGroupID(clientGID, connOracle);

                //【01】取得未讀訊息
                //
                string selStr = "SELECT r.GMsgID, m.CreateTime, m.MsgType, m.MsgPreview, "
                  + " m.Sender, m.MsgContent, m.UpdateObj"
                  + " FROM group_msg_read r, group_msg m"
                  + " WHERE r.GMsgID=m.pid AND r.Receiver=:Receiver AND m.GroupID=:GroupID"
                  + "    AND m.CreateTime >:ReceiveID AND m.Status NOT IN ('F','L')"
                  + " ORDER BY m.CreateTime, r.GMsgID";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("Receiver", OracleType.VarChar).Value = userInfo.SIP_Num;
                selCmd.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                selCmd.Parameters.Add("ReceiveID", OracleType.DateTime).Value = receiveID;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<Dictionary<string, string>> MessageList = new List<Dictionary<string, string>>();
                List<string> MsgIdList = new List<string>();

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    Dictionary<string, string> message = new Dictionary<string, string>();
                    string MsgType = Convert.ToString(row["MsgType"]);
                    message["MsgID"] = Convert.ToString(row["GMsgID"]);
                    message["MsgTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", row["CreateTime"]);
                    message["MsgType"] = MsgType;
                    message["MsgSender"] = Convert.ToString(row["Sender"]);
                    message["Content"] = Convert.ToString(row["MsgContent"]);
                    message["MsgPreview"] = Convert.ToString(row["MsgPreview"]);

                    if (MsgType == "GJ" || MsgType == "GL")
                    {
                        message["UpdateObj"] = Convert.ToString(row["UpdateObj"]);
                    }
                    MessageList.Add(message);

                    MsgIdList.Add(message["MsgID"]);
                }


                if (MsgIdList.Count > 0)
                {
                    //【02】寫入讀取時間
                    //
                    string updStr = "UPDATE group_msg_read SET ReadTime=:DateTime"
                      + " WHERE ReadTime IS NULL AND Receiver=:Receiver AND GMsgID IN "
                      + string.Format("({0})", string.Join(",", MsgIdList.ToArray()));
                    OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                    updCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    updCmd.Parameters.Add("Receiver", OracleType.VarChar).Value = userInfo.SIP_Num;
                    updCmd.ExecuteNonQuery();
                }


                //【99】回傳結果
                //
                Dictionary<string, object> resultDict = new Dictionary<string, object>();
                resultDict["ChatID"] = clientGID;
                resultDict["Receiver"] = userInfo.SIP_Num;
                resultDict["Messages"] = MessageList.ToArray();
                return resultDict;
            }
            finally
            {
                connOracle.Close();
            }
        }

        /// <summary>
        /// 檢查訊息檔案存取權限(群組訊息)
        /// </summary>
        /// <param name="userInfo">SipUserInfo物件</param>
        /// <param name="GMsgID">Server端的群組訊息ID</param>
        /// <param name="filename">要存取的檔案名稱</param>
        /// <returns>可以存取true，否則false</returns>
        public static bool canAccessGroupFile(SipUserInfo userInfo, long GMsgID, string filename)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string selStr = "SELECT m.MsgContent"
                  + " FROM group_msg_read r, group_msg m"
                  + " WHERE r.GMsgID=m.pid AND r.GMsgID=:GMsgID"
                  + " AND :SIP_Num IN (r.Receiver, m.Sender)";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("GMsgID", OracleType.Int32).Value = GMsgID;
                selCmd.Parameters.Add("SIP_Num", OracleType.VarChar).Value = userInfo.SIP_Num;
                string MsgContent = Convert.ToString(selCmd.ExecuteScalar());

                if (string.IsNullOrEmpty(MsgContent))
                    return false;
                else if (MsgContent.Contains(filename))
                    return true;
                else
                    return false;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 讀取 Read Tag(群組訊息)
        /// </summary>
        /// <param name="userInfo">發訊方SipUserInfo物件</param>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <returns>Read Tag Dictionary</returns>
        public static Dictionary<string, object> TagGroupMsgRead(SipUserInfo userInfo, string clientGID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();

                List<Dictionary<string, string>> TagList = new List<Dictionary<string, string>>();
                List<string> MsgIdList = new List<string>();
                int GroupID = OracleKitGroup.getGroupID(clientGID, connOracle);
                DateTime reqTime = DateTime.Now;

                //【01】取得有新Read Tag的群組訊息ID
                //
                string selStr1 = "SELECT m.pid AS GMsgID, m.LocalPID"
                  + " FROM group_msg m, group_msg_read r"
                  + " WHERE m.pid=r.GMsgID AND m.Sender=:Sender AND m.GroupID=:GroupID"
                  + " AND m.Status NOT IN ('F','L')"
                  + " AND r.ReadTime>= CASE WHEN m.ReadTagTime IS NULL THEN m.CreateTime ELSE m.ReadTagTime END"
                  + " AND r.ReadTime<=:reqTime"
                  + " GROUP BY m.pid, m.LocalPID";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                selCmd1.Parameters.Add("Sender", OracleType.VarChar).Value = userInfo.SIP_Num;
                selCmd1.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                selCmd1.Parameters.Add("reqTime", OracleType.DateTime).Value = reqTime;
                OracleDataAdapter adapter1 = new OracleDataAdapter(selCmd1);
                DataSet dataSet1 = new DataSet();
                adapter1.Fill(dataSet1);

                myTrans = connOracle.BeginTransaction();

                foreach (DataRow row in dataSet1.Tables[0].Rows)
                {
                    long GMsgID = Convert.ToInt64(row["GMsgID"]);

                    //【02】取得多人已讀筆數
                    //
                    string countStr2 = "SELECT COUNT(*) FROM group_msg_read"
                      + " WHERE GMsgID=:GMsgID AND ReadTime IS NOT NULL";
                    OracleCommand countCmd2 = new OracleCommand(countStr2, connOracle, myTrans);
                    countCmd2.Parameters.Add("GMsgID", OracleType.Number).Value = GMsgID;
                    int ReadCount = Convert.ToInt32(countCmd2.ExecuteScalar());

                    Dictionary<string, string> readTag = new Dictionary<string, string>();
                    readTag["MsgID"] = Convert.ToString(GMsgID);
                    readTag["LocalPID"] = Convert.ToString(row["LocalPID"]);
                    readTag["ReadCount"] = Convert.ToString(ReadCount);
                    TagList.Add(readTag);

                    MsgIdList.Add(readTag["MsgID"]);


                    //【03】檢查是否全部已讀
                    //
                    string countStr3 = "SELECT COUNT(*) FROM group_msg_read WHERE GMsgID=:GMsgID";
                    OracleCommand countCmd3 = new OracleCommand(countStr3, connOracle, myTrans);
                    countCmd3.Parameters.Add("GMsgID", OracleType.Number).Value = GMsgID;
                    int ReceiverCount = Convert.ToInt32(countCmd3.ExecuteScalar());
                    if (ReadCount == ReceiverCount)
                    {
                        //【03-1】更新訊息狀態(全部已讀並標記)
                        //
                        string updStr3 = "UPDATE group_msg SET Status='F' WHERE pid=:GMsgID";
                        OracleCommand updCmd3 = new OracleCommand(updStr3, connOracle, myTrans);
                        updCmd3.Parameters.Add("GMsgID", OracleType.Int32).Value = GMsgID;
                        updCmd3.ExecuteNonQuery();
                    }
                }


                if (MsgIdList.Count > 0)
                {
                    //【04】更新Read Tag時間
                    //
                    string updStr4 = "UPDATE group_msg SET ReadTagTime=:ReadTagTime"
                      + " WHERE pid IN "
                      + string.Format("({0})", string.Join(",", MsgIdList.ToArray()));
                    OracleCommand updCmd4 = new OracleCommand(updStr4, connOracle, myTrans);
                    updCmd4.Parameters.Add("ReadTagTime", OracleType.DateTime).Value = reqTime;
                    updCmd4.ExecuteNonQuery();
                }


                //【98】Commit Transaction
                //
                myTrans.Commit();


                //【99】回傳結果
                //
                Dictionary<string, object> resultDict = new Dictionary<string, object>();
                resultDict["ChatID"] = clientGID;
                resultDict["Sender"] = userInfo.SIP_Num;
                resultDict["ReadTags"] = TagList.ToArray();
                return resultDict;
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        #endregion



        /// <summary>
        /// 設定訊息提醒(Push通知)狀態
        /// </summary>
        /// <param name="SIP_Num">User SIP帳號</param>
        /// <param name="ChatID">聊天ID(對方帳號或ClientGID)</param>
        /// <param name="OnOff">訊息提醒On或Off</param>
        public static void SetMsgNotify(string SIP_Num, string ChatID, bool OnOff)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                if (OnOff)
                {
                    //【01】開啟訊息提醒
                    //
                    string updStr1 = "UPDATE msg_notify_off"
                      + " SET DisableTime=:DateTime"
                      + " WHERE DisableTime IS NULL AND SIP_Num=:SIP_Num AND ChatID=:ChatID";
                    OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle);
                    updCmd1.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    updCmd1.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                    updCmd1.Parameters.Add("ChatID", OracleType.VarChar).Value = ChatID;
                    updCmd1.ExecuteNonQuery();
                }
                else
                {
                    //【02】關閉訊息提醒
                    //
                    string selStr = "SELECT COUNT(*) FROM msg_notify_off"
                      + " WHERE DisableTime IS NULL AND SIP_Num=:SIP_Num AND ChatID=:ChatID";
                    OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                    selCmd.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                    selCmd.Parameters.Add("ChatID", OracleType.VarChar).Value = ChatID;
                    int count = Convert.ToInt32(selCmd.ExecuteScalar());

                    if (count == 0)
                    {
                        string insStr2 = "INSERT INTO msg_notify_off"
                          + " (CreateTime, SIP_Num, ChatID) VALUES"
                          + " (:DateTime, :SIP_Num, :ChatID)";
                        OracleCommand insCmd2 = new OracleCommand(insStr2, connOracle);
                        insCmd2.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                        insCmd2.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                        insCmd2.Parameters.Add("ChatID", OracleType.VarChar).Value = ChatID;
                        insCmd2.ExecuteNonQuery();
                    }
                }
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 檢查群組訊息是否存在
        /// </summary>
        /// <param name="GMsgID">Server端的群組訊息ID</param>
        /// <param name="Sender">發訊人帳號</param>
        /// <returns>有此群組訊息true，否則false</returns>
        public static bool hadGroupMsg(long GMsgID, string Sender)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string countStr = "SELECT COUNT(*) FROM group_msg"
                  + " WHERE pid=:GMsgID AND Sender=:Sender";
                OracleCommand countCmd = new OracleCommand(countStr, connOracle);
                countCmd.Parameters.Add("GMsgID", OracleType.Int32).Value = GMsgID;
                countCmd.Parameters.Add("Sender", OracleType.VarChar).Value = Sender;
                int count = Convert.ToInt32(countCmd.ExecuteScalar());
                return (count > 0);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得Server端的群組訊息ID
        /// </summary>
        /// <param name="ClientGID">Client端的群組ID</param>
        /// <param name="Sender">發訊人帳號</param>
        /// <param name="LocalPID">Client端的群組訊息ID</param>
        /// <returns>有此群組訊息回傳ID，否則-1</returns>
        public static long getGroupMsgID(string ClientGID, string Sender, int LocalPID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                int GroupID = OracleKitGroup.getGroupID(ClientGID, connOracle);

                string selStr = "SELECT pid FROM group_msg"
                  + " WHERE GroupID=:GroupID AND Sender=:Sender AND LocalPID=:LocalPID"
                  + " ORDER BY CreateTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                selCmd.Parameters.Add("Sender", OracleType.VarChar).Value = Sender;
                selCmd.Parameters.Add("LocalPID", OracleType.Int32).Value = LocalPID;
                object selResult = selCmd.ExecuteScalar();

                if (selResult == DBNull.Value)
                    return -1;
                else
                    return Convert.ToInt64(selResult);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得群組訊息的已讀/未讀清單資訊
        /// </summary>
        /// <param name="GMsgID">Server端的群組訊息ID</param>
        /// <returns>群組訊息的已讀/未讀清單資訊</returns>
        public static Dictionary<string, string>[] getGroupReadInfo(long GMsgID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string selStr = "SELECT r.Receiver, u.CustomName, u.DeviceType, r.ReadTime"
                  + " FROM group_msg_read r, reg_user u"
                  + " WHERE u.Status='Y' AND r.Receiver=u.SIP_Num AND r.GMsgID=:GMsgID"
                  + " ORDER BY r.ReadTime, r.Receiver";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("GMsgID", OracleType.Int32).Value = GMsgID;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                int rowCount = dataSet.Tables[0].Rows.Count;
                Dictionary<string, string>[] resultArray = new Dictionary<string, string>[rowCount];

                for (int i = 0; i < rowCount; i++)
                {
                    DataRow row = dataSet.Tables[0].Rows[i];
                    Dictionary<string, string> infoDict = new Dictionary<string, string>();
                    infoDict["Receiver"] = Convert.ToString(row["Receiver"]);

                    string iDeviceFamily = AppleKit.getIDeviceFamily(Convert.ToString(row["DeviceType"]));
                    infoDict["DisplayName"] = string.Format("{0} {1}", row["CustomName"], iDeviceFamily);

                    infoDict["ReadTime"] = "";
                    if (row["ReadTime"] != DBNull.Value)
                    {
                        DateTime ReadTime = Convert.ToDateTime(row["ReadTime"]);
                        if (ReadTime.Date == DateTime.Today)
                            infoDict["ReadTime"] = string.Format("Today {0:HH:mm}", ReadTime);
                        else if (ReadTime.Year == DateTime.Now.Year)
                            infoDict["ReadTime"] = ReadTime.ToString("M/d");
                        else
                            infoDict["ReadTime"] = ReadTime.ToString("yyyy/M/d");
                    }

                    resultArray[i] = infoDict;
                }
                return resultArray;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>取得歷史訊息(單人聊天)</summary>
        /// <param name="userInfo">收訊方SipUserInfo物件</param>
        /// <param name="sender">發訊方帳號</param>
        /// <param name="Pid">取得這筆訊息以前的歷史訊息</param>
        /// <param name="MessageCount">歷史訊息筆數</param>
        /// <returns>歷史訊息Dictionary</returns>
        public static Dictionary<string, object> GetHistoryOneOneMsgs(SipUserInfo userInfo, string sender, Int64 Pid, int MessageCount)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());

            try
            {
                connOracle.Open();

                //【01】取得歷史訊息
                string selStr = "SELECT * FROM ("
                  + " SELECT pid, CreateTime, Sender, Receiver, MsgType, MsgContent"
                  + " FROM msg_one"
                  + " WHERE Status<>'W'"
                  + " AND Sender IN (:Sender, :Receiver)"
                  + " AND Receiver IN (:Receiver, :Sender)"
                  + " AND Sender <> Receiver"
                  + " AND pid < :Pid"
                  + " ORDER BY CreateTime DESC)"
                  + " WHERE ROWNUM <= :MessageCount";  //2015/03/11改成Oracle限制筆數寫法
                //+ " LIMIT :MessageCount";   //MySQL限制筆數寫法
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add(":Sender", OracleType.VarChar).Value = sender;
                selCmd.Parameters.Add(":Receiver", OracleType.VarChar).Value = userInfo.SIP_Num;
                selCmd.Parameters.Add(":Pid", OracleType.Int32).Value = Pid;
                selCmd.Parameters.Add(":MessageCount", OracleType.Int32).Value = MessageCount;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<Dictionary<string, string>> MessageList = new List<Dictionary<string, string>>();
                List<string> MsgIdList = new List<string>();

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    Dictionary<string, string> message = new Dictionary<string, string>();
                    message["MsgID"] = Convert.ToString(row["pid"]);
                    message["MsgTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", row["CreateTime"]);
                    message["MsgType"] = Convert.ToString(row["MsgType"]);
                    message["MsgSender"] = Convert.ToString(row["Sender"]);
                    message["MsgReceiver"] = Convert.ToString(row["Receiver"]);
                    message["Content"] = Convert.ToString(row["MsgContent"]);
                    MessageList.Add(message);
                }

                MessageList.Reverse();

                //【99】回傳結果
                Dictionary<string, object> resultDict = new Dictionary<string, object>();
                resultDict["Messages"] = MessageList.ToArray();
                return resultDict;
            }
            finally
            {
                connOracle.Close();
            }
        }

        /// <summary>取得歷史訊息(群組聊天)</summary>
        /// <param name="userInfo">收訊方SipUserInfo物件</param>
        /// <param name="clientGID">Client端的群組ID</param>
        /// <param name="Pid">取得這筆訊息以前的歷史訊息</param>
        /// <param name="MessageCount">歷史訊息筆數</param>
        /// <returns>歷史訊息Dictionary</returns>
        public static Dictionary<string, object> GetHistoryGroupMsgs(SipUserInfo userInfo, string clientGID, Int64 Pid, int MessageCount)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                int GroupID = OracleKitGroup.getGroupID(clientGID, connOracle);

                //【01】取得未讀訊息
                string selStr = "SELECT * FROM ("
                  + " SELECT Pid, CreateTime, Sender, MsgType, MsgContent"
                  + " FROM group_msg"
                  + " WHERE GroupID=:GroupID"
                  + " AND Status<>'W'"
                  + " AND Pid < :Pid"
                  + " ORDER BY CreateTime DESC)"
                  + " WHERE ROWNUM <= :MessageCount";  //2015/04/01改成Oracle限制筆數寫法
                //+ " LIMIT :MessageCount";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("GroupID", OracleType.Int32).Value = GroupID;
                selCmd.Parameters.Add("Pid", OracleType.Int32).Value = Pid;
                selCmd.Parameters.Add("MessageCount", OracleType.Int32).Value = MessageCount;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<Dictionary<string, string>> MessageList = new List<Dictionary<string, string>>();
                List<string> MsgIdList = new List<string>();

                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    Dictionary<string, string> message = new Dictionary<string, string>();
                    message["MsgID"] = Convert.ToString(row["Pid"]);
                    message["MsgTime"] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", row["CreateTime"]);
                    message["MsgType"] = Convert.ToString(row["MsgType"]);
                    message["MsgSender"] = Convert.ToString(row["Sender"]);
                    message["Content"] = Convert.ToString(row["MsgContent"]);
                    MessageList.Add(message);
                }

                MessageList.Reverse();

                //【99】回傳結果
                Dictionary<string, object> resultDict = new Dictionary<string, object>();
                resultDict["ChatID"] = clientGID;
                resultDict["Receiver"] = userInfo.SIP_Num;
                resultDict["Messages"] = MessageList.ToArray();
                return resultDict;
            }
            finally
            {
                connOracle.Close();
            }
        }

    }


    /// <summary>
    /// 未讀訊息Preview物件
    /// </summary>
    [Serializable()]
    public class UnreadMsgPreview : IComparable
    {
        private string _ChatID;
        private UInt32 _UnreadCount;
        private DateTime _LastTime;
        private string _LastPreview;
        private DateTime _FirstTime;

        public string ChatID { get { return this._ChatID; } }
        public UInt32 UnreadCount { get { return this._UnreadCount; } }
        public DateTime LastTime { get { return this._LastTime; } }
        public string LastPreview { get { return this._LastPreview; } }
        public DateTime FirstTime { get { return this._FirstTime; } }

        public UnreadMsgPreview(DataRow row, string lastPreview)
        {
            _ChatID = Convert.ToString(row["ChatID"]);
            _UnreadCount = Convert.ToUInt32(row["UnreadCount"]);
            _LastTime = Convert.ToDateTime(row["LastTime"]);
            _LastPreview = lastPreview;
            _FirstTime = Convert.ToDateTime(row["FirstTime"]);
        }

        public int CompareTo(object obj)
        {
            if (obj is UnreadMsgPreview)
                return this.CompareTo((UnreadMsgPreview)obj);
            else
                throw new Exception("ERROR_CANNOT_COMPARE");
        }

        public int CompareTo(UnreadMsgPreview previewObj)
        {
            int result = _LastTime.CompareTo(previewObj._LastTime);
            if (result == 0)
                return _ChatID.CompareTo(previewObj._ChatID);
            else
                return result;
        }
    }
}